﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqExpenses
    {
        public List<Expense> GetExpenses(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<ExpenseEntity> query = db.ExpenseEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "ExpenseID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.ExpenseID);
                            else
                                query = query.OrderByDescending(c => c.ExpenseID);
                            break;

                    }
                }
                return query.Select(c => Mapper.ToExpenseObject(c)).ToList();
            }
        }


        public Expense GetExpense(int expenseId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToExpenseObject(db.ExpenseEntities
                            .SingleOrDefault(p => p.ExpenseID == expenseId));
            }
        }




        public Expense GetExpensesForCustomer(string customerId)
        {

            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                Expense exp = new Expense();

                
                                                       
                                                       

                var expense = (from exps in db.ExpenseEntities
                               where exps.CustomerID == customerId
                               group exps by new { exps.CustomerID } into g
                               select new Expense
                              {
                                  ExpenseId = g.FirstOrDefault().ExpenseID,
                                  CustomerId = g.FirstOrDefault().CustomerID,
                                  Food = (float)g.Sum(p => (double?)p.Food).GetValueOrDefault(),
                                  HouseExp = (float)g.Sum(p => (double?)p.HouseExp).GetValueOrDefault(),
                                  Transport = (float)g.Sum(p => (double?)p.Transport).GetValueOrDefault(),
                                  SchFees = (float)g.Sum(p => (double?)p.SchFees).GetValueOrDefault(),
                                  OtherExp = (float)g.Sum(p => (double?)p.OtherExp).GetValueOrDefault(),
                                  LoanInst = (float)g.Sum(p => (double?)p.LoanInstalments).GetValueOrDefault(),
                                  CreditPay = (float)g.Sum(p => (double?)p.CreditPay).GetValueOrDefault(),

                                 

                              });

                if (expense.FirstOrDefault() != null)
                {
                    exp.ExpenseId = expense.FirstOrDefault().ExpenseId;
                    exp.CustomerId = expense.FirstOrDefault().CustomerId;
                    exp.Food = expense.FirstOrDefault().Food;
                    exp.HouseExp = expense.FirstOrDefault().HouseExp;
                    exp.Transport = expense.FirstOrDefault().Transport;
                    exp.SchFees = expense.FirstOrDefault().SchFees;
                    exp.OtherExp = expense.FirstOrDefault().OtherExp;
                    exp.LoanInst = expense.FirstOrDefault().LoanInst;
                    exp.CreditPay = expense.FirstOrDefault().CreditPay;

                }
                else
                {
                    exp.Food = 0;
                    exp.HouseExp = 0;
                    exp.Transport = 0;
                    exp.SchFees = 0;
                    exp.OtherExp = 0;
                    exp.LoanInst = 0;
                    exp.CreditPay = 0;
                }

                return exp;

            }


            //using (SODataDataContext db = DataContextFactory.CreateContext())
            //{
            //    Expense inco = new Expense();

            //    IQueryable<ExpenseEntity> expenses =  from exp in db.ExpenseEntities
            //                                           where exp.CustomerID == customerId
            //                                           select exp;


            //    return expenses.Select(c => Mapper.ToExpenseObject(c)).ToList();

            //}

        }





        public void InsertExpense(Expense expense)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ExpenseEntity entity = Mapper.ToExpenseEntity(expense);
                    db.ExpenseEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    expense.ExpenseId = entity.ExpenseID;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to expenses record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert expenses " + expense.ExpenseName);
                }
            }
        }

        public int UpdateExpense(Expense expense)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ExpenseEntity entity = Mapper.ToExpenseEntity(expense);
                    db.ExpenseEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to expenses record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeleteExpense(Expense expense)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ExpenseEntity entity = Mapper.ToExpenseEntity(expense);
                    db.ExpenseEntities.Attach(entity, false);
                    db.ExpenseEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to expense record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

    }
}
